﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using XLETL.BLL;
using XLETL.Model;
using XLETL.Common;
using XLETL.TransformationPluginManager;
using System.Reflection;
using System.IO;

namespace XLETL.ETLEngine
{
    
  public class ETLEngine
    {
        ETL etlService = null;

        public string StartETLProcess(object taskId)
        {
          // Record the start Date Time
          DateTime etlStartDateTime = DateTime.Now;

          StringBuilder sbLog = new StringBuilder(string.Format("ETL Process Starting at {0}", etlStartDateTime));
          sbLog.AppendLine();
            sbLog.AppendLine("-----------------------------------------------------");
            sbLog.AppendLine();

          TaskInfo taskToProcess = null;
          try
          {
            bool resultFlag = true;

            if (taskId == null)
            {
              throw new ArgumentNullException("taskId");
            }

            Guid taskID = (Guid)taskId;
            Task task = new Task();
            taskToProcess = task.GetTaskDetails(taskID);

            if (taskToProcess != null)
            {
              sbLog.AppendFormat("-- Task info (TaskID: {0}) is loaded", taskID);
              sbLog.AppendLine();
            }
            else
            {
              throw new ApplicationException(string.Format("Error loading task (TaskID: {0}) info for processing", taskID));
            }

            DB db = new DB();
            DBInfo parentDb = db.GetDBDetails(taskToProcess.DatabaseID);

            if (parentDb != null)
            {
              sbLog.AppendFormat("-- Database {0}'s info (Database ID: {1}) is loaded", parentDb.DBFriendlyName, parentDb.DBId);
              sbLog.AppendLine();
            }
            else
            {
              throw new ApplicationException(string.Format("Error loading target database (Database ID: {0}) info", taskToProcess.DatabaseID));
            }

            string dataFilePath = GetFileToProcess(taskToProcess.FileUploadPath);

            if (string.IsNullOrEmpty(dataFilePath))
            {
              sbLog.AppendFormat("-- File to process not found (File Path: {0})", taskToProcess.FileUploadPath);
              sbLog.AppendLine();
            }
            else
            {
              // 1. Extract XL data
              ExcelHelper eh = new ExcelHelper();
              DataTable loadedData = eh.LoadSpecifiedSheet(dataFilePath, taskToProcess.TaskDetails.SourceSheetName);
              
              // Save state to the log  
              sbLog.AppendFormat("-- File {0} is loaded for processing", dataFilePath);
              sbLog.AppendLine();

              // make sure there are some data 
              if (loadedData.Rows.Count > 0)
              {
                // 2. Validate loaded sheet
                resultFlag = ValidateLoadedSheetColumnConsistency(loadedData, taskToProcess);

                if (resultFlag)
                {
                  // Save state to the log  
                  sbLog.Append("-- Data file validated successfuly");
                  sbLog.AppendLine();

                  // 3. Execute Template Transformations
                  resultFlag = ExecuteTransformation(loadedData, taskToProcess, parentDb);

                  if (resultFlag)
                  {
                    // Save state to the log  
                    sbLog.Append("-- Source Transformations processed successfuly");
                    sbLog.AppendLine();

                    // 4. Executre Target Transformations
                    resultFlag = ExecuteTargetColumnTransformation(loadedData, taskToProcess, parentDb);

                    // 5. Load Data
                    if (resultFlag)
                    {
                      // Save state to the log  
                      sbLog.Append("-- Target Transformations processed successfuly");
                      sbLog.AppendLine();

                      string sqlQuery = BuildSqlScripts(loadedData, taskToProcess, parentDb);
                      if (!string.IsNullOrEmpty(sqlQuery))
                      {
                        resultFlag = LoadDataIntoTargetTable(sqlQuery, parentDb, false);
                        if (resultFlag)
                        {
                          // Save state to the log  
                          sbLog.AppendFormat("-- Data loaded succesfuly into {0}", taskToProcess.TargetTableName);
                          sbLog.AppendLine();

                          // Now rename file as processed
                          File.Move(dataFilePath, dataFilePath.Replace(".xlsm", string.Format(".done_{0:yyMMddHHmm}", etlStartDateTime)));

                          // Save state to the log  
                          sbLog.AppendFormat("-- Processed file marked as done (*.done_{0:yyMMddHHmm})", etlStartDateTime);
                          sbLog.AppendLine();

                          // Now create log file for the processed file
                          Utilities.LogData(sbLog.ToString(), taskToProcess.FileUploadPath, etlStartDateTime); 
                          // Save the sql query into the log as well
                          Utilities.LogData(sqlQuery, taskToProcess.FileUploadPath, etlStartDateTime);                          
                        
                        }
                        else
                        {
                          // Save state to the log  
                          //Utilities.LogData("Error occured while loading data", taskToProcess.FileUploadPath);
                          sbLog.Append("Error occured while loading data");
                          sbLog.AppendLine();
                        }
                      }
                    }
                  }
                }
              }             

            }

          }
          catch (Exception ex)
          {
            sbLog.AppendLine("Error occured: ");
            sbLog.AppendFormat("Error message: \r\n{0}", ex.Message);
            sbLog.AppendLine();
            sbLog.AppendFormat("Stack Trace: \r\n{0}", ex.StackTrace);
            sbLog.AppendLine();

            // In case of error create log file as well
            Utilities.LogData(sbLog.ToString(), taskToProcess.FileUploadPath, etlStartDateTime);
          }
          finally
          {
            // clean up resources            
          }

          return sbLog.ToString();
        }

        public string GetFileToProcess(string dir)
        {
          DirectoryInfo directory = new DirectoryInfo(dir);
          FileInfo[] files = directory.GetFiles("*.xlsm");

          if (files.Length > 0)
          {
            return files[0].FullName;
          }
          else return "";
        }

        /// <summary>
        /// Method to validate Source Columns' Name, Order, Type
        /// </summary>
        /// <param name="loadedSheet"></param>
        /// <param name="taskInfo"></param>
        /// <returns></returns>
        public bool ValidateLoadedSheetColumnConsistency(DataTable loadedSheet, TaskInfo taskInfo)
        {
            bool isValid = true;

            object columnName = null;
            Type columnType = null;

            // Validate for the columns consistency (same number of columns with correct headings and correct column type)
            for (int i = 0; i < taskInfo.SourceColumns.Count; i++)
            {
                columnName = loadedSheet.Columns[i].ColumnName;
                
                string type = Common.Utilities.GetDescriptionAttrFromEnum(typeof(Globals.ColumnType), taskInfo.SourceColumns[i].Type);
                columnType = System.Type.GetType(type);

                // if equality fails for the heading name and column type then exit
                if (!taskInfo.SourceColumns[i].Name.Equals(columnName) && !columnType.Equals(loadedSheet.Columns[i].DataType))
                {
                    isValid = false;
                    break;
                }
            }
            
            return isValid;
        }

        public bool ExecuteTransformation(DataTable loadedSheet, TaskInfo taskInfo, DBInfo companyInfo)
        {
            bool isSuccess = true;

            // check for transformations
            foreach (SourceColumnInfo sci in taskInfo.SourceColumns)
            {
                if (sci.Transformations != null && sci.Transformations.Count > 0)
                {
                    // Send data for transformation                    
                    foreach (PluginInfo pluginInfo in sci.Transformations)
                    {
                        // Instantiate Transformation Plugin
                        IPluginControl currentPlugin = InstantiatePlugin(pluginInfo, taskInfo, companyInfo);
                        if (currentPlugin != null)
                        {
                            isSuccess = currentPlugin.DoTransformation(loadedSheet, sci.Name);
                        } 
                    }
                }
            }

            return isSuccess;
        }

        /// <summary>
        /// Process Target Column Transformation while merging Target and Source Columns
        /// </summary>
        /// <param name="loadedSheet"></param>
        /// <param name="taskInfo"></param>
        /// <param name="dbInfo"></param>
        /// <returns></returns>
        public bool ExecuteTargetColumnTransformation(DataTable loadedSheet, TaskInfo taskInfo, DBInfo dbInfo)
        {
            bool isSuccess = true;

            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
                SourceColumnInfo sci = taskInfo.SourceColumns.Find(delegate(SourceColumnInfo search)
                {
                    if (search.TargetColumnID.Equals(tci.ID))
                        return true;
                    else
                        return false;
                });

                // if target column has no mapping with source then create the similiar in the loadedSheet
                if (sci == null)
                {
                    // Create column 
                    SqlDbType retval = (SqlDbType)System.Enum.Parse(typeof(SqlDbType), tci.Type, true);
                    Type columType = Utilities.GetSystemType(retval);
                    loadedSheet.Columns.Add(tci.ID.ToString(), columType);

                    // execute transformations
                    if (tci.Transformations != null && tci.Transformations.Count > 0)
                    {
                        // Send data for transformation                    
                        foreach (PluginInfo pluginInfo in tci.Transformations)
                        {
                            // Create Transformation Plugin
                            IPluginControl currentPlugin = InstantiatePlugin(pluginInfo, taskInfo, dbInfo);
                            if (currentPlugin != null)
                            {
                                isSuccess = currentPlugin.DoTransformation(loadedSheet, tci.ID.ToString());
                            }
                        }
                    }                   
                }else {                                       
                    // replace the column name
                    loadedSheet.Columns[sci.Name].ColumnName = sci.TargetColumnID.ToString();
                }
            }           

            return isSuccess;
        }
        
        /// <summary>
        /// Method to create instance of Transformation Plugin control 
        /// </summary>
        /// <param name="plugin"></param>
        /// <param name="taskInfo"></param>
        /// <param name="companyInfo"></param>
        /// <returns></returns>
        private IPluginControl InstantiatePlugin(PluginInfo plugin, TaskInfo taskInfo, DBInfo companyInfo)
        {
          string filePath = System.IO.Path.Combine(GlobalCache.PLUGIN_STORAGE_FOLDER, plugin.AssemblyFile);
          Type t = Assembly.LoadFile(filePath).GetType(plugin.Type);

            Object[] prms = new Object[2];

            if (plugin.PluginParamList != null && plugin.PluginParamList.Length > 0)
            {
                for (int i = 0; i < plugin.PluginParamList.Length; i++)
                {
                    // if parameter type equals to DbConnString or DbTableName then assign specific value
                    if (plugin.PluginParamList[i].ParameterType.Equals(PluginInfo.PARAM_GLOBAL_DB_CONN_STRING))
                    {
                        plugin.PluginParamList[i].ParameterValue = companyInfo.ConnString;
                    }                    
                }
            }

            // now put the retrieved params into the object array
            prms[0] = plugin.PluginParamList;
            prms[1] = plugin.TransformationParamList;

            IPluginControl pluginControl = (IPluginControl)Activator.CreateInstance(t, prms);

            return pluginControl;
        }

        public string BuildSqlScripts(DataTable loadedData, TaskInfo taskInfo, DBInfo dbInfo)
        {
            bool isSuccess = false;
            // TODO: implement proper exception handling

            if (etlService == null)
                etlService = new ETL(dbInfo.DALClassName);

            StringBuilder sqlQuery = new StringBuilder();
            StringBuilder sqlValues = null;
            SqlDbType colType;

            // 1. Build Sql script pattern based on the target column list
            switch (taskInfo.TaskDetails.ProcessMode)
            {
                case Globals.LoadMode.InsertAll: // Build:  Insert Into [Table] ([Column1],[Column2]) Values({0});

                    // build the Insert into statement
                    string sqlInsertAllPattern = etlService.BuildInsertStatement(taskInfo, false);

                    sqlValues = new StringBuilder();                    
                
                    for (int i = 0; i < loadedData.Rows.Count; i++)
                    {
                        foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
                        {
                            if (!tci.IsIdentity && !tci.Name.ToLower().Equals("rowguid")) // ignore autoincrement and rowguid columns
                            {
                                colType = (SqlDbType)System.Enum.Parse(typeof(SqlDbType), tci.Type, true);
                                sqlValues.AppendFormat("{0},", GetFormatedValue(colType, tci.IsDefaultNull, loadedData.Rows[i][tci.ID.ToString()]));
                            }
                        }

                        sqlValues.Remove(sqlValues.Length - 1, 1); // remove the last ,

                        // inject the values into the insert statement
                        sqlQuery.AppendFormat(sqlInsertAllPattern, sqlValues.ToString());
                        sqlQuery.AppendLine();
                        sqlValues.Remove(0, sqlValues.Length); // clear the StringBuilder for the next iteration
                    }
                    break;

                case Globals.LoadMode.InsertOrUpdate: // Build: Create Table #XLETLTEMPDATA(Username nvarchar(50), Password nvarchar(50), ActionID int );
                case Globals.LoadMode.InsertNew:
                case Globals.LoadMode.InsertOrDelete:
                //sqlQuery.AppendFormat(etlService.GetStartTranString, taskInfo.TargetTableName);
                    //sqlQuery.AppendLine();

                    sqlQuery.Append(etlService.BuildCreateTempTableStatement(taskInfo));
                    // build the Insert into statement
                    string sqlQueryInsertPattern = etlService.BuildInsertStatement(taskInfo, true);

                    sqlValues = new StringBuilder();                    
                    //object rowValue = null;
                    for (int i = 0; i < loadedData.Rows.Count; i++)
                    {
                      //if (IsRowValid(loadedData.Rows[i], taskInfo.TargetColumns.Count))
                      {
                        foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
                        {
                          if (!tci.IsIdentity && !tci.Name.ToLower().Equals("rowguid")) // ignore autoincrement and rowguid columns
                          {
                            colType = (SqlDbType)System.Enum.Parse(typeof(SqlDbType), tci.Type, true);
                            sqlValues.AppendFormat("{0},", GetFormatedValue(colType, tci.IsDefaultNull, loadedData.Rows[i][tci.ID.ToString()]));
                          }
                        }
                      }

                        sqlValues.Remove(sqlValues.Length - 1, 1); // remove the last ,

                        // inject the values into the insert statement
                        sqlQuery.AppendFormat(sqlQueryInsertPattern, sqlValues.ToString());
                        sqlQuery.AppendLine();
                        sqlValues.Remove(0, sqlValues.Length); // clear the StringBuilder for the next iteration
                    }

                    // Update Temp table's ActionID
                    sqlQuery.AppendLine(etlService.BuildUpdateTempTableStatement(taskInfo));

                    // Insert new records
                    sqlQuery.AppendLine(etlService.BuildInsertNewStatement(taskInfo));

                    switch (taskInfo.TaskDetails.ProcessMode)
                    {
                        case Globals.LoadMode.InsertOrUpdate:
                            // Update existing records
                            sqlQuery.AppendLine(etlService.BuildUpdateExistingStatement(taskInfo));
                            break;
                        case Globals.LoadMode.InsertOrDelete:
                            sqlQuery.AppendLine(etlService.BuildDeleteExistingStatement(taskInfo));
                            break;
                        case Globals.LoadMode.InsertNew:
                            break;
                    }                    

                    sqlQuery.AppendFormat(etlService.GetDropTableString, taskInfo.TargetTableName);
                    sqlQuery.AppendLine();                    

                    break;
                
                case Globals.LoadMode.Update:
                    break;

                case Globals.LoadMode.Delete:
                    break;                    
            }
          
          return sqlQuery.ToString();
        }

        private bool IsRowValid(DataRow row, int columnCount)
        {
          bool isValid = true;
          for (int c = 0; c < columnCount; c++)
          {            
            object val = row[c];
            if (val == null || val == DBNull.Value)
            {
              isValid = false;
            }
          }

          return isValid;
        }
        
        public bool LoadDataIntoTargetTable(string sqlQuery, DBInfo dbInfo, bool testRun)
        {
            bool isSuccess = false;

            if (etlService == null)
                etlService = new ETL(dbInfo.DALClassName);

            isSuccess = etlService.ExecuteSqlScript(sqlQuery, dbInfo.ConnString, testRun);

            return isSuccess;
        }

        private string GetFormatedValue(SqlDbType type, bool isNullable, object value)
        {
            string valueToReturn = string.Empty;
            if (value != null || value != DBNull.Value)
                valueToReturn = value.ToString().Replace("'", "''");
            
            // if value is emptey then check if null allowed
            if (valueToReturn.Equals(string.Empty) && isNullable)
            {
                valueToReturn = "NULL";
            }
            else
            {
                switch (type)
                {
                    case SqlDbType.NChar:
                    case SqlDbType.NVarChar:
                    case SqlDbType.VarChar:
                    case SqlDbType.Char:
                    case SqlDbType.Image:
                    case SqlDbType.NText:
                    case SqlDbType.Text:
                    case SqlDbType.Bit:
                    case SqlDbType.UniqueIdentifier:
                        valueToReturn = string.Format("'{0}'", valueToReturn);
                        break;
                    case SqlDbType.Int:
                    case SqlDbType.Money:
                        valueToReturn = string.Format("{0}", valueToReturn);
                        break;
                    case SqlDbType.DateTime:
                        DateTime parsedDateTime;
                        bool isSuccess = DateTime.TryParse(value.ToString(), out parsedDateTime);
                        if (isSuccess)
                        {
                            valueToReturn = string.Format("'{0}'", parsedDateTime.ToString("s"));
                        }
                        else
                        {
                            valueToReturn = "''";
                        }
                        break;
                    case SqlDbType.Decimal:
                        valueToReturn = string.Format("'{0}'", valueToReturn.Replace(",", "."));
                        break;

                }
            }

            return valueToReturn;
        }
    }
}
